/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.apt.matrimony.methods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apt.matrimony.db.ConvertDateTime;
import org.apt.matrimony.db.MyConnection;

/**
 *
 * @author yoko
 */
public class Updates {

    // Cập nhật AccountDetail
    public static int updateAccountDetail(int album_ID, String username, String Full_Name, String Gender, String Date_Of_Birth, String Marital_Status, String Mother_Tongue, float Height, int Contact_Number, String Caste, String Address, int Liking_Id, String Family_Details) {

        Connection cnn = null;
        PreparedStatement pst = null;
        int update = 0;
        String[] columnName = {"Album_ID", "Full_Name", "Gender", "Date_Of_Birth", "Marital_Status", "Mother_Tongue", "Height", "Contact_Number", "Caste", "Address", "Liking_Id", "Family_Details"};
        String sql = sqlUpdateComm("Account_Details", columnName, "User_Name", username);

        try {

            cnn = MyConnection.connType4();
            pst = cnn.prepareCall(sql);

            pst.setInt(1, album_ID);
            pst.setString(2, Full_Name);
            pst.setString(3, Gender);
            pst.setString(4, Date_Of_Birth);
            pst.setString(5, Marital_Status);
            pst.setString(6, Mother_Tongue);
            pst.setFloat(7, Height);
            pst.setInt(8, Contact_Number);
            pst.setString(9, Address);
            pst.setString(10, Family_Details);


            update = pst.executeUpdate();
            if (update > 0) {
                System.out.println("Thanh cong");
            } else {

                System.out.println("That bai");
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyConnection.closeConnection(cnn, pst, null);

        }

        return update;
    }

    // Thay đổi Password
    public static int updatePass(String username, String oldPass, String newPass) {
        Connection cnn = null;
        PreparedStatement pst = null;
        int update = 0;
        String[] columnName = {"User_Password"};
        String sql = sqlUpdateComm("Accounts", columnName, "User_Name", username);
        boolean flag = false;

        try {
            flag = validateAccount.checkPass(username, oldPass);
            if (flag) {
                cnn = MyConnection.connType4();
                pst = cnn.prepareCall(sql);

                pst.setString(1, newPass);
                update = pst.executeUpdate();
                if (update > 0) {
                    System.out.println("Update Pass Success!");
                } else {
                    System.out.println("Update Password fail");
                }
            }else{
                System.out.println("Old - Password is not Correct!!");
            }
        } catch (SQLException ex) {
            Logger.getLogger(Updates.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            MyConnection.closeConnection(cnn, pst, null);

        }
        return update;
    }

    //Thiết lập ngày tồn tại tài khoản:
    public static int UpdateDate_Expired(String pay, String username) {
        Connection cnn = null;
        PreparedStatement pst = null;
        String dayofAcc = Updates.getDate_Ex(username);
        String date = ConvertDateTime.addDate(pay, dayofAcc);
        int update = 0;
        String[] columnName = {"Date_Expired"};
        String sql = sqlUpdateComm("Accounts", columnName, "User_Name", username);


        try {
            cnn = MyConnection.connType4();
            pst = cnn.prepareCall(sql);
            pst.setString(1, date);
            update = pst.executeUpdate();
            if (update > 0) {
                System.out.println("Update Pass Success!");
            } else {
                System.out.println("Update Password fail");
            }

        } catch (SQLException ex) {
            Logger.getLogger(Updates.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            MyConnection.closeConnection(cnn, pst, null);

        }
        return update;
    }

    //Thay đổi ngày hết hạn trong tài khoản
    public static String getDate_Ex(String username) {
        Connection cnn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String accountDate_Expired = null;
        String sqlGetAcc = "SELECT * FROM Accounts  WHERE User_Name = '" + username + "'";


        try {
            cnn = MyConnection.connType4();
            pst = cnn.prepareCall(sqlGetAcc);
            rs = pst.executeQuery();
            while (rs.next()) {
                accountDate_Expired = rs.getString("Date_Expired");
            }

        } catch (SQLException ex) {
            Logger.getLogger(Updates.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            MyConnection.closeConnection(cnn, pst, null);

        }

        return accountDate_Expired;
    }

    public static String sqlUpdateComm(String tblName, String[] columnName, String whereColmn, Object value) {

        String sql = "UPDATE  " + tblName + " SET ";
        if (columnName != null) {
            for (int i = 0; i < columnName.length - 1; i++) {
                sql += " " + columnName[i] + " = ?, ";
            }
            sql += columnName[columnName.length - 1] + " = ? WHERE " + whereColmn + " = '" + value + "'";


        }
        System.out.println("SQL - Update " + tblName + ": " + sql);

        return sql;
    }

    public static int getAccountID(String username) {
        Connection cnn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int account = 0;
        String sqlGetAcc = "SELECT * FROM Accounts  WHERE User_Name = '" + username + "'";


        try {
            System.out.println(sqlGetAcc);
            cnn = MyConnection.connType4();
            pst = cnn.prepareCall(sqlGetAcc);
            rs = pst.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("User_Name"));
                account = rs.getInt("Account_Id");
                System.out.println("Account ID cua " + username + " la: " + account);
            }

        } catch (SQLException ex) {
            Logger.getLogger(Updates.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            MyConnection.closeConnection(cnn, pst, null);

        }



        return account;
    }

    public static int updteCountries(int country_Id, String countryName) {
        int insert = 0;
        Connection cnn = null;
        PreparedStatement pst = null;


        String tblName = "Countries";
        String[] columnName = {"Country_Name"};

        String sql = sqlUpdateComm(tblName, columnName, "Country_ID", country_Id);


        try {
            cnn = MyConnection.connType4();
            pst = cnn.prepareCall(sql);


            pst.setString(1, countryName);

            insert = pst.executeUpdate();
            if (insert > 0) {
                System.out.println("Thanh cong");
            } else {

                System.out.println("That bai");
            }


        } catch (SQLException ex) {
            Logger.getLogger(Inserts.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            MyConnection.closeConnection(cnn, pst, null);

        }

        return insert;

    }

    public static void main(String[] args) {
        int test = Updates.getAccountID("admin");

    }
}
